{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "happy-magazine",
   "metadata": {},
   "source": [
    "This file is meant as a helper file for the project. \n",
    "After turning pgn into .csv file, we save it, then we will load it alongside the kaggle dataset and combine the two into one final dataset to feed the chess app.\n",
    "Columns in the kaggle dataset: </br>\n",
    "id, created_at, last_move_at, turns, victory_status, winner, increment_code, white_rating, black_rating, moves, opening_eco, opening_name, opening_ply, endFEN, mated_by, nr_of_pieces, pawns, knights, bishops, rooks, queens, wKing_sqr, bKing_sqr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "minimal-director",
   "metadata": {},
   "outputs": [],
   "source": [
    "import dtale\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import chess\n",
    "import chess.pgn\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "announced-commander",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Defines dict to map notation of pieces to their respective string representation\n",
    "pieces_san_dict = {\"P\":\"Pawn\",\"N\":\"Knight\",\"B\":\"Bishop\",\"R\":\"Rook\",\"Q\":\"Queen\",\"K\":\"King\"}\n",
    "\n",
    "# Define function that returns which piece delivered mate, by parsing the moves column.\n",
    "def mate_piece(move_list):\n",
    "    last_move = move_list[-1]\n",
    "    if \"#\" in last_move:\n",
    "        #consider that pawns don't have uppecase lettering. \n",
    "        if last_move[0].isupper():\n",
    "            return pieces_san_dict[last_move[0]]\n",
    "        else:\n",
    "            return 'Pawn'\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "comparable-assessment",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read PGN files.\n",
    "# pgn_file = \"lichess_elite_2014-05.pgn\" \n",
    "pgn_file = \"lichess_db_standard_rated_2017-04.pgn\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "pending-senior",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of games' headers parsed: 500000\n",
      "Total number of games parsed: 500000\n"
     ]
    }
   ],
   "source": [
    "# First, skim through the pgn to extract data from the headers.\n",
    "\n",
    "# Columns to extract.\n",
    "extracting = [\"Event\",\"UTCDate\",\"Result\",\"WhiteElo\",\"BlackElo\",\"Termination\",\"TimeControl\",\"ECO\",\"Opening\"]\n",
    "\n",
    "# Initialize a dict that will be the base for the output DF.\n",
    "df_dict = {k: [] for k in extracting+[\"fens\",\"moves\",\"turns\",\"mated_by\"]}\n",
    "\n",
    "# Extract headers.\n",
    "# Note that read_headers actually reads the file in order, so to start over we need to open it again.\n",
    "pgn = open(pgn_file)\n",
    "\n",
    "iterations = 0\n",
    "while iterations<500000:\n",
    "        \n",
    "    headers = chess.pgn.read_headers(pgn)\n",
    "    if headers is None:\n",
    "        break\n",
    "        \n",
    "    for header in extracting:\n",
    "        df_dict[header].append(headers[header])\n",
    "    iterations += 1\n",
    "\n",
    "print(\"Total number of games' headers parsed: {}\".format(iterations))\n",
    "amt_games = iterations\n",
    "    \n",
    "# Extract the other columns.\n",
    "# This defines the read_game argument to read all the way to the end of the game and return the final position of the board, which is what we want.\n",
    "pgn = open(pgn_file)\n",
    "board_builder = chess.pgn.BoardBuilder\n",
    "\n",
    "iterations = 0\n",
    "while iterations < amt_games:\n",
    "    finalboard = chess.pgn.read_game(pgn,Visitor=board_builder)\n",
    "    df_dict[\"moves\"].append(finalboard.fullmove_number)\n",
    "    df_dict[\"turns\"].append(finalboard.ply())\n",
    "    if finalboard.is_checkmate():\n",
    "        df_dict[\"mated_by\"].append(pieces_san_dict[finalboard.piece_at(finalboard.peek().to_square).symbol().upper()])\n",
    "    else:\n",
    "        df_dict[\"mated_by\"].append(None)\n",
    "    df_dict[\"fens\"].append(finalboard.fen())\n",
    "\n",
    "    iterations += 1\n",
    "\n",
    "print(\"Total number of games parsed: {}\".format(iterations))\n",
    "\n",
    "\n",
    "#print(df_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "elder-construction",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turn df_dict into a dataframe, that matches the one on Kaggle Dataset.\n",
    "df = pd.DataFrame(df_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "diverse-portrait",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Event</th>\n",
       "      <th>UTCDate</th>\n",
       "      <th>Result</th>\n",
       "      <th>WhiteElo</th>\n",
       "      <th>BlackElo</th>\n",
       "      <th>Termination</th>\n",
       "      <th>TimeControl</th>\n",
       "      <th>ECO</th>\n",
       "      <th>Opening</th>\n",
       "      <th>fens</th>\n",
       "      <th>moves</th>\n",
       "      <th>turns</th>\n",
       "      <th>mated_by</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1-0</td>\n",
       "      <td>2186</td>\n",
       "      <td>1907</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+0</td>\n",
       "      <td>C34</td>\n",
       "      <td>King's Gambit Accepted, Schallopp Defense</td>\n",
       "      <td>r1b2q1b/pp1pkB1r/2pN4/4P1Q1/1n1P3P/2N5/PPPK1RP...</td>\n",
       "      <td>19</td>\n",
       "      <td>37</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rated Bullet game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1-0</td>\n",
       "      <td>1385</td>\n",
       "      <td>1339</td>\n",
       "      <td>Time forfeit</td>\n",
       "      <td>120+1</td>\n",
       "      <td>C34</td>\n",
       "      <td>King's Gambit Accepted, King's Knight Gambit</td>\n",
       "      <td>6k1/pp1b1p1p/1b1B1Q2/3B4/3P3N/2P3K1/PP4PP/r7 b...</td>\n",
       "      <td>24</td>\n",
       "      <td>47</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1-0</td>\n",
       "      <td>1905</td>\n",
       "      <td>1836</td>\n",
       "      <td>Normal</td>\n",
       "      <td>180+0</td>\n",
       "      <td>C63</td>\n",
       "      <td>Ruy Lopez: Schliemann Defense, Schoenemann Attack</td>\n",
       "      <td>4B2r/6b1/5p1k/2p4Q/4pP2/8/PPP3RK/8 b - - 0 33</td>\n",
       "      <td>33</td>\n",
       "      <td>65</td>\n",
       "      <td>Queen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rated Bullet game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>0-1</td>\n",
       "      <td>1670</td>\n",
       "      <td>1812</td>\n",
       "      <td>Normal</td>\n",
       "      <td>120+1</td>\n",
       "      <td>A00</td>\n",
       "      <td>Hungarian Opening</td>\n",
       "      <td>8/1kpp1N2/4p1n1/1p2Pp2/2n2Pp1/P3K1P1/8/8 w - -...</td>\n",
       "      <td>38</td>\n",
       "      <td>74</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>0-1</td>\n",
       "      <td>988</td>\n",
       "      <td>1145</td>\n",
       "      <td>Time forfeit</td>\n",
       "      <td>180+0</td>\n",
       "      <td>A00</td>\n",
       "      <td>Van't Kruijs Opening</td>\n",
       "      <td>r3k3/1p3pp1/p1p5/3pN3/1P1P4/P1NP4/1R6/2K2q2 w ...</td>\n",
       "      <td>32</td>\n",
       "      <td>62</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Event     UTCDate Result WhiteElo BlackElo   Termination  \\\n",
       "0   Rated Blitz game  2017.03.31    1-0     2186     1907        Normal   \n",
       "1  Rated Bullet game  2017.03.31    1-0     1385     1339  Time forfeit   \n",
       "2   Rated Blitz game  2017.03.31    1-0     1905     1836        Normal   \n",
       "3  Rated Bullet game  2017.03.31    0-1     1670     1812        Normal   \n",
       "4   Rated Blitz game  2017.03.31    0-1      988     1145  Time forfeit   \n",
       "\n",
       "  TimeControl  ECO                                            Opening  \\\n",
       "0       180+0  C34          King's Gambit Accepted, Schallopp Defense   \n",
       "1       120+1  C34       King's Gambit Accepted, King's Knight Gambit   \n",
       "2       180+0  C63  Ruy Lopez: Schliemann Defense, Schoenemann Attack   \n",
       "3       120+1  A00                                  Hungarian Opening   \n",
       "4       180+0  A00                               Van't Kruijs Opening   \n",
       "\n",
       "                                                fens  moves  turns mated_by  \n",
       "0  r1b2q1b/pp1pkB1r/2pN4/4P1Q1/1n1P3P/2N5/PPPK1RP...     19     37     None  \n",
       "1  6k1/pp1b1p1p/1b1B1Q2/3B4/3P3N/2P3K1/PP4PP/r7 b...     24     47     None  \n",
       "2      4B2r/6b1/5p1k/2p4Q/4pP2/8/PPP3RK/8 b - - 0 33     33     65    Queen  \n",
       "3  8/1kpp1N2/4p1n1/1p2Pp2/2n2Pp1/P3K1P1/8/8 w - -...     38     74     None  \n",
       "4  r3k3/1p3pp1/p1p5/3pN3/1P1P4/P1NP4/1R6/2K2q2 w ...     32     62     None  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "pregnant-decline",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(500000, 13)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "outdoor-train",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bk = df.copy()\n",
    "#df = df_bk.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "miniature-boards",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manipulate columns to resemble the kaggle dataset.\n",
    "# Drop some columns.\n",
    "#df.drop(columns=\"Date\",inplace=True)\n",
    "\n",
    "## Fix dtypes.\n",
    "df[\"WhiteElo\"] = df[\"WhiteElo\"].astype(int)\n",
    "df[\"BlackElo\"] = df[\"BlackElo\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "least-jason",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Result -> Winner\n",
    "replace_dict = {\"1-0\":\"white\",\"0-1\":\"black\",'1/2-1/2':\"draw\"}\n",
    "df[\"Winner\"] = df[\"Result\"].replace(to_replace=replace_dict)\n",
    "df.drop(columns=\"Result\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "conditional-chaos",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Tiago\\.conda\\envs\\JupyterLab v2x\\lib\\site-packages\\pandas\\core\\indexing.py:1637: SettingWithCopyWarning:\n",
      "\n",
      "\n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Termination -> Victory Status. In kaggle theres outoftime, resign, draw, and mate.\n",
    "## index of different conditions that will make a new column.\n",
    "draw_idx = np.where(df[\"Winner\"]==\"draw\")\n",
    "time_idx = np.where(df[\"Termination\"]==\"Time forfeit\")\n",
    "mate_idx = np.where(df[\"mated_by\"].isna()==False)\n",
    "\n",
    "## The rest will be filled with resign, as there is no other option left.\n",
    "df[\"victory_status\"] = \"resign\"\n",
    "df[\"victory_status\"].iloc[draw_idx[0]] = \"draw\"\n",
    "df[\"victory_status\"].iloc[time_idx[0]] = \"outoftime\"\n",
    "df[\"victory_status\"].iloc[mate_idx[0]] = \"mate\"\n",
    "try:\n",
    "    df.drop(columns=\"Termination\",inplace=True)\n",
    "except:\n",
    "    pass\n",
    "## who cares if this raises warnings, warnings are not errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "entertaining-horse",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the same functions as to Kaggle dataset to derive the remaining columns.\n",
    "\n",
    "# Code in this cell is adapted from https://andreasstckl.medium.com/chessviz-graphs-of-chess-games-7ebd4f85a9b9\n",
    "# Define a function that coverts the FEN notation into a tensor representation of the board.\n",
    "\n",
    "def fen_to_tensor(input_str):\n",
    "    # creates a dictionary of pieces. 1 to 6 is white pieces, -1 to -6 is black pieces.\n",
    "    pieces_str = \"PNBRQKpnbrqk\"\n",
    "    pieces = set(pieces_str)\n",
    "    pieces_dict = {pieces_str[0]:1, pieces_str[1]:2, pieces_str[2]:3, pieces_str[3]:4 , \n",
    "                    pieces_str[4]:5, pieces_str[5]:6,\n",
    "                    pieces_str[6]:-1, pieces_str[7]:-2, pieces_str[8]:-3, pieces_str[9]:-4, \n",
    "                    pieces_str[10]:-5, pieces_str[11]:-6}\n",
    "    \n",
    "    # creates a tensor representation of the board. \n",
    "    # This tensor represents a 8*8 board for each of the 6 possible pieces.\n",
    "    # The first two axis are the coordinates, the third axis is whether there is a piece there or not.\n",
    "    # If 1, there is a white piece there. If -1, there is a black piece there.\n",
    "    # For example, if the value at position (0,0,0) is 1, then there is a white pawn at the A8 sqr.\n",
    "    board_tensor = np.zeros((8,8,6))\n",
    "    input_list = input_str.split()\n",
    "    rownr = 0\n",
    "    colnr = 0\n",
    "    valid_spaces = set(range(1,9))\n",
    "    \n",
    "    # As input list is a split FEN, index 0 is the pos of each piece on the board.\n",
    "    # This loop goes through every element of the FEN which is the same as looking at each square of the board in sequence and seeing what piece is there.\n",
    "    for i,c in enumerate(input_list[0]):\n",
    "        if c in pieces:\n",
    "            board_tensor[rownr,colnr,np.abs(pieces_dict[c])-1] = np.sign(pieces_dict[c])\n",
    "            colnr = colnr + 1\n",
    "        elif c == \"/\": \n",
    "            #a backslash means the row is over, so we go to the next row\n",
    "            rownr += 1\n",
    "            colnr = 0\n",
    "        elif int(c) in valid_spaces:\n",
    "            # a number means that many squares in the row are empty.\n",
    "            colnr += int(c)\n",
    "        else:\n",
    "            raise ValueError(\"invalid fenstr at index: {} char: {}\".format(i, c))\n",
    "        \n",
    "    return board_tensor\n",
    "\n",
    "# Define other useful functions for these tensors.\n",
    "# Counts the total amount of pieces on the board.\n",
    "def count_pieces(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    return np.sum(np.abs(board_tensor))\n",
    "\n",
    "# This outputs a tuple containing the total amt of pawns, knights, bishops, rooks, and queens in the board.\n",
    "def count_all_pieces(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    counts = np.sum(np.abs(board_tensor),axis=(0,1))\n",
    "    return counts[0],counts[1],counts[2],counts[3],counts[4]\n",
    "\n",
    "# This outputs both King's square position\n",
    "def king_squares(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    king_col,king_row=np.where(board_tensor[:,:,5]==1)\n",
    "    white_king_coordinates = king_col[0],king_row[0]\n",
    "    king_col,king_row=np.where(board_tensor[:,:,5]==-1)\n",
    "    black_king_coordinates = king_col[0],king_row[0]\n",
    "    \n",
    "    return white_king_coordinates,black_king_coordinates\n",
    "\n",
    "def queen_squares(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    try:\n",
    "        queen_col,queen_row=np.where(board_tensor[:,:,4]==1)\n",
    "        white_queen_coordinates = queen_col[0],queen_row[0]\n",
    "    except:\n",
    "        white_queen_coordinates = (None,None)\n",
    "        \n",
    "    try:\n",
    "        queen_col,queen_row=np.where(board_tensor[:,:,4]==-1)\n",
    "        black_queen_coordinates = queen_col[0],queen_row[0]\n",
    "    except:\n",
    "        black_queen_coordinates = (None,None)\n",
    "        \n",
    "    \n",
    "    return white_queen_coordinates,black_queen_coordinates\n",
    "\n",
    "def rook_squares(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    try:\n",
    "        rook_col,rook_row=np.where(board_tensor[:,:,3]==1)\n",
    "        white_rook_coordinates = rook_col[0],rook_row[0]\n",
    "    except:\n",
    "        white_rook_coordinates = (None,None)\n",
    "    try:\n",
    "        white_rook2_coordinates = rook_col[1],rook_row[1]\n",
    "    except:\n",
    "        white_rook2_coordinates = (None,None)\n",
    "        \n",
    "    try:\n",
    "        rook_col,rook_row=np.where(board_tensor[:,:,3]==-1)\n",
    "        black_rook_coordinates = rook_col[0],rook_row[0]\n",
    "    except:\n",
    "        black_rook_coordinates = (None,None)\n",
    "    try:\n",
    "        black_rook2_coordinates = rook_col[1],rook_row[1]\n",
    "    except:\n",
    "        black_rook2_coordinates = (None,None)\n",
    "        \n",
    "    \n",
    "    return white_rook_coordinates,black_rook_coordinates,white_rook2_coordinates,black_rook2_coordinates\n",
    "\n",
    "def bishop_squares(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    try:\n",
    "        bishop_col,bishop_row=np.where(board_tensor[:,:,2]==1)\n",
    "        white_bishop_coordinates = bishop_col[0],bishop_row[0]\n",
    "    except:\n",
    "        white_bishop_coordinates = (None,None)\n",
    "    try:\n",
    "        white_bishop2_coordinates = bishop_col[1],bishop_row[1]\n",
    "    except:\n",
    "        white_bishop2_coordinates = (None,None)\n",
    "        \n",
    "    try:\n",
    "        bishop_col,bishop_row=np.where(board_tensor[:,:,2]==-1)\n",
    "        black_bishop_coordinates = bishop_col[0],bishop_row[0]\n",
    "    except:\n",
    "        black_bishop_coordinates = (None,None)\n",
    "    try:\n",
    "        black_bishop2_coordinates = bishop_col[1],bishop_row[1]\n",
    "    except:\n",
    "        black_bishop2_coordinates = (None,None)\n",
    "        \n",
    "    \n",
    "    return white_bishop_coordinates,black_bishop_coordinates,white_bishop2_coordinates,black_bishop2_coordinates\n",
    "\n",
    "def knight_squares(fen):\n",
    "    board_tensor = fen_to_tensor(fen)\n",
    "    try:\n",
    "        knight_col,knight_row=np.where(board_tensor[:,:,1]==1)\n",
    "        white_knight_coordinates = knight_col[0],knight_row[0]\n",
    "    except:\n",
    "        white_knight_coordinates = (None,None)\n",
    "    try:\n",
    "        white_knight2_coordinates = knight_col[1],knight_row[1]\n",
    "    except:\n",
    "        white_knight2_coordinates = (None,None)\n",
    "        \n",
    "    try:\n",
    "        knight_col,knight_row=np.where(board_tensor[:,:,1]==-1)\n",
    "        black_knight_coordinates = knight_col[0],knight_row[0]\n",
    "    except:\n",
    "        black_knight_coordinates = (None,None)\n",
    "    try:\n",
    "        black_knight2_coordinates = knight_col[1],knight_row[1]\n",
    "    except:\n",
    "        black_knight2_coordinates = (None,None)\n",
    "        \n",
    "    \n",
    "    return white_knight_coordinates,black_knight_coordinates,white_knight2_coordinates,black_knight2_coordinates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "arabic-certification",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Now let's use the FENS and functions we have to derive more columns.\n",
    "\n",
    "df.rename(columns={\"fens\":\"endFEN\"},inplace=True)\n",
    "# Total amount of pieces on the board.\n",
    "df[\"nr_of_pieces\"] = df[\"endFEN\"].apply(count_pieces)\n",
    "\n",
    "# gets the nr of each piece on the board\n",
    "temp = df[\"endFEN\"].apply(count_all_pieces)\n",
    "for i,piece in enumerate([\"pawns\",\"knights\",\"bishops\",\"rooks\",\"queens\"]):\n",
    "    df[piece] = temp.apply(lambda x:x[i])\n",
    "# This might not be very useful because it doesn't discern between white and black pieces, but that is okay for now. \n",
    "# It can be used for filters based on presence of pieces at endgame.\n",
    "# We can use the number of pieces and turns to define arbitrarily if the game ended in the opening, midgame, or endgame.\n",
    "\n",
    "# Defines columns for both kings positions\n",
    "temp = df[\"endFEN\"].apply(king_squares)\n",
    "for i,sqr in enumerate([\"wKing_sqr\",\"bKing_sqr\"]):\n",
    "    df[sqr] = temp.apply(lambda x:x[i])\n",
    "\n",
    "# Defines columns for both queens positions\n",
    "temp = df[\"endFEN\"].apply(queen_squares)\n",
    "for i,sqr in enumerate([\"wQueen_sqr\",\"bQueen_sqr\"]):\n",
    "    df[sqr] = temp.apply(lambda x:x[i])\n",
    "\n",
    "# Defines columns for all rook positions\n",
    "temp = df[\"endFEN\"].apply(rook_squares)\n",
    "for i,sqr in enumerate([\"wRook_sqr\",\"bRook_sqr\",\"wRook2_sqr\",\"bRook2_sqr\"]):\n",
    "    df[sqr] = temp.apply(lambda x:x[i])\n",
    "    \n",
    "# Defines columns for all bishop positions\n",
    "temp = df[\"endFEN\"].apply(bishop_squares)\n",
    "for i,sqr in enumerate([\"wBishop_sqr\",\"bBishop_sqr\",\"wBishop2_sqr\",\"bBishop2_sqr\"]):\n",
    "    df[sqr] = temp.apply(lambda x:x[i])\n",
    "    \n",
    "# Defines columns for all knight positions\n",
    "temp = df[\"endFEN\"].apply(knight_squares)\n",
    "for i,sqr in enumerate([\"wKnight_sqr\",\"bKnight_sqr\",\"wKnight2_sqr\",\"bKnight2_sqr\"]):\n",
    "    df[sqr] = temp.apply(lambda x:x[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "solved-radius",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Derive more columns if necessary.\n",
    "df[\"avg_Elo\"] = df.loc[:,\"WhiteElo\":\"BlackElo\"].mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "hindu-jumping",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Event</th>\n",
       "      <th>UTCDate</th>\n",
       "      <th>WhiteElo</th>\n",
       "      <th>BlackElo</th>\n",
       "      <th>TimeControl</th>\n",
       "      <th>ECO</th>\n",
       "      <th>Opening</th>\n",
       "      <th>endFEN</th>\n",
       "      <th>moves</th>\n",
       "      <th>turns</th>\n",
       "      <th>...</th>\n",
       "      <th>bRook2_sqr</th>\n",
       "      <th>wBishop_sqr</th>\n",
       "      <th>bBishop_sqr</th>\n",
       "      <th>wBishop2_sqr</th>\n",
       "      <th>bBishop2_sqr</th>\n",
       "      <th>wKnight_sqr</th>\n",
       "      <th>bKnight_sqr</th>\n",
       "      <th>wKnight2_sqr</th>\n",
       "      <th>bKnight2_sqr</th>\n",
       "      <th>avg_Elo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>2186</td>\n",
       "      <td>1907</td>\n",
       "      <td>180+0</td>\n",
       "      <td>C34</td>\n",
       "      <td>King's Gambit Accepted, Schallopp Defense</td>\n",
       "      <td>r1b2q1b/pp1pkB1r/2pN4/4P1Q1/1n1P3P/2N5/PPPK1RP...</td>\n",
       "      <td>19</td>\n",
       "      <td>37</td>\n",
       "      <td>...</td>\n",
       "      <td>(1, 7)</td>\n",
       "      <td>(1, 5)</td>\n",
       "      <td>(0, 2)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(0, 7)</td>\n",
       "      <td>(2, 3)</td>\n",
       "      <td>(4, 1)</td>\n",
       "      <td>(5, 2)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>2046.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rated Bullet game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1385</td>\n",
       "      <td>1339</td>\n",
       "      <td>120+1</td>\n",
       "      <td>C34</td>\n",
       "      <td>King's Gambit Accepted, King's Knight Gambit</td>\n",
       "      <td>6k1/pp1b1p1p/1b1B1Q2/3B4/3P3N/2P3K1/PP4PP/r7 b...</td>\n",
       "      <td>24</td>\n",
       "      <td>47</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(2, 3)</td>\n",
       "      <td>(1, 3)</td>\n",
       "      <td>(3, 3)</td>\n",
       "      <td>(2, 1)</td>\n",
       "      <td>(4, 7)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1362.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1905</td>\n",
       "      <td>1836</td>\n",
       "      <td>180+0</td>\n",
       "      <td>C63</td>\n",
       "      <td>Ruy Lopez: Schliemann Defense, Schoenemann Attack</td>\n",
       "      <td>4B2r/6b1/5p1k/2p4Q/4pP2/8/PPP3RK/8 b - - 0 33</td>\n",
       "      <td>33</td>\n",
       "      <td>65</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(0, 4)</td>\n",
       "      <td>(1, 6)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1870.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rated Bullet game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1670</td>\n",
       "      <td>1812</td>\n",
       "      <td>120+1</td>\n",
       "      <td>A00</td>\n",
       "      <td>Hungarian Opening</td>\n",
       "      <td>8/1kpp1N2/4p1n1/1p2Pp2/2n2Pp1/P3K1P1/8/8 w - -...</td>\n",
       "      <td>38</td>\n",
       "      <td>74</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(1, 5)</td>\n",
       "      <td>(2, 6)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(4, 2)</td>\n",
       "      <td>1741.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>988</td>\n",
       "      <td>1145</td>\n",
       "      <td>180+0</td>\n",
       "      <td>A00</td>\n",
       "      <td>Van't Kruijs Opening</td>\n",
       "      <td>r3k3/1p3pp1/p1p5/3pN3/1P1P4/P1NP4/1R6/2K2q2 w ...</td>\n",
       "      <td>32</td>\n",
       "      <td>62</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(3, 4)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(5, 2)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1066.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1367</td>\n",
       "      <td>1396</td>\n",
       "      <td>180+0</td>\n",
       "      <td>A00</td>\n",
       "      <td>Hungarian Opening</td>\n",
       "      <td>r4rk1/ppp1q1pp/2p2pn1/8/8/1P2P1Pb/PbPNNP1P/1R1...</td>\n",
       "      <td>14</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>(0, 5)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(5, 7)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(6, 1)</td>\n",
       "      <td>(6, 3)</td>\n",
       "      <td>(2, 6)</td>\n",
       "      <td>(6, 4)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1381.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Rated Blitz game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1683</td>\n",
       "      <td>1832</td>\n",
       "      <td>180+0</td>\n",
       "      <td>C00</td>\n",
       "      <td>French Defense: Knight Variation</td>\n",
       "      <td>8/8/8/5k2/3K4/6p1/1P2B3/8 b - - 0 51</td>\n",
       "      <td>51</td>\n",
       "      <td>101</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(6, 4)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1757.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Rated Bullet game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1515</td>\n",
       "      <td>1576</td>\n",
       "      <td>120+0</td>\n",
       "      <td>B10</td>\n",
       "      <td>Caro-Kann Defense: Accelerated Panov Attack #2</td>\n",
       "      <td>8/5p1k/7P/8/4pK2/8/pb3P2/8 w - - 0 49</td>\n",
       "      <td>49</td>\n",
       "      <td>96</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(6, 1)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1545.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Rated Classical game</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1822</td>\n",
       "      <td>1885</td>\n",
       "      <td>300+8</td>\n",
       "      <td>C64</td>\n",
       "      <td>Ruy Lopez: Classical Variation, Central Variation</td>\n",
       "      <td>4r3/6pk/R2p3p/2p5/5N2/1PB2N1P/1P2r1PK/8 b - - ...</td>\n",
       "      <td>31</td>\n",
       "      <td>61</td>\n",
       "      <td>...</td>\n",
       "      <td>(6, 4)</td>\n",
       "      <td>(5, 2)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(4, 5)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(5, 5)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1853.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Rated Classical tournament https://lichess.org...</td>\n",
       "      <td>2017.03.31</td>\n",
       "      <td>1766</td>\n",
       "      <td>1768</td>\n",
       "      <td>600+0</td>\n",
       "      <td>C16</td>\n",
       "      <td>French Defense: Winawer Variation, Advance Var...</td>\n",
       "      <td>8/6k1/7p/2Q2pn1/3Pq3/6PK/5P1P/8 w - - 7 42</td>\n",
       "      <td>42</td>\n",
       "      <td>82</td>\n",
       "      <td>...</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(3, 6)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>(None, None)</td>\n",
       "      <td>1767.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Event     UTCDate  WhiteElo  \\\n",
       "0                                   Rated Blitz game  2017.03.31      2186   \n",
       "1                                  Rated Bullet game  2017.03.31      1385   \n",
       "2                                   Rated Blitz game  2017.03.31      1905   \n",
       "3                                  Rated Bullet game  2017.03.31      1670   \n",
       "4                                   Rated Blitz game  2017.03.31       988   \n",
       "5                                   Rated Blitz game  2017.03.31      1367   \n",
       "6                                   Rated Blitz game  2017.03.31      1683   \n",
       "7                                  Rated Bullet game  2017.03.31      1515   \n",
       "8                               Rated Classical game  2017.03.31      1822   \n",
       "9  Rated Classical tournament https://lichess.org...  2017.03.31      1766   \n",
       "\n",
       "   BlackElo TimeControl  ECO  \\\n",
       "0      1907       180+0  C34   \n",
       "1      1339       120+1  C34   \n",
       "2      1836       180+0  C63   \n",
       "3      1812       120+1  A00   \n",
       "4      1145       180+0  A00   \n",
       "5      1396       180+0  A00   \n",
       "6      1832       180+0  C00   \n",
       "7      1576       120+0  B10   \n",
       "8      1885       300+8  C64   \n",
       "9      1768       600+0  C16   \n",
       "\n",
       "                                             Opening  \\\n",
       "0          King's Gambit Accepted, Schallopp Defense   \n",
       "1       King's Gambit Accepted, King's Knight Gambit   \n",
       "2  Ruy Lopez: Schliemann Defense, Schoenemann Attack   \n",
       "3                                  Hungarian Opening   \n",
       "4                               Van't Kruijs Opening   \n",
       "5                                  Hungarian Opening   \n",
       "6                   French Defense: Knight Variation   \n",
       "7     Caro-Kann Defense: Accelerated Panov Attack #2   \n",
       "8  Ruy Lopez: Classical Variation, Central Variation   \n",
       "9  French Defense: Winawer Variation, Advance Var...   \n",
       "\n",
       "                                              endFEN  moves  turns  ...  \\\n",
       "0  r1b2q1b/pp1pkB1r/2pN4/4P1Q1/1n1P3P/2N5/PPPK1RP...     19     37  ...   \n",
       "1  6k1/pp1b1p1p/1b1B1Q2/3B4/3P3N/2P3K1/PP4PP/r7 b...     24     47  ...   \n",
       "2      4B2r/6b1/5p1k/2p4Q/4pP2/8/PPP3RK/8 b - - 0 33     33     65  ...   \n",
       "3  8/1kpp1N2/4p1n1/1p2Pp2/2n2Pp1/P3K1P1/8/8 w - -...     38     74  ...   \n",
       "4  r3k3/1p3pp1/p1p5/3pN3/1P1P4/P1NP4/1R6/2K2q2 w ...     32     62  ...   \n",
       "5  r4rk1/ppp1q1pp/2p2pn1/8/8/1P2P1Pb/PbPNNP1P/1R1...     14     27  ...   \n",
       "6               8/8/8/5k2/3K4/6p1/1P2B3/8 b - - 0 51     51    101  ...   \n",
       "7              8/5p1k/7P/8/4pK2/8/pb3P2/8 w - - 0 49     49     96  ...   \n",
       "8  4r3/6pk/R2p3p/2p5/5N2/1PB2N1P/1P2r1PK/8 b - - ...     31     61  ...   \n",
       "9         8/6k1/7p/2Q2pn1/3Pq3/6PK/5P1P/8 w - - 7 42     42     82  ...   \n",
       "\n",
       "     bRook2_sqr   wBishop_sqr   bBishop_sqr  wBishop2_sqr  bBishop2_sqr  \\\n",
       "0        (1, 7)        (1, 5)        (0, 2)  (None, None)        (0, 7)   \n",
       "1  (None, None)        (2, 3)        (1, 3)        (3, 3)        (2, 1)   \n",
       "2  (None, None)        (0, 4)        (1, 6)  (None, None)  (None, None)   \n",
       "3  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)   \n",
       "4  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)   \n",
       "5        (0, 5)  (None, None)        (5, 7)  (None, None)        (6, 1)   \n",
       "6  (None, None)        (6, 4)  (None, None)  (None, None)  (None, None)   \n",
       "7  (None, None)  (None, None)        (6, 1)  (None, None)  (None, None)   \n",
       "8        (6, 4)        (5, 2)  (None, None)  (None, None)  (None, None)   \n",
       "9  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)   \n",
       "\n",
       "    wKnight_sqr   bKnight_sqr  wKnight2_sqr  bKnight2_sqr avg_Elo  \n",
       "0        (2, 3)        (4, 1)        (5, 2)  (None, None)  2046.5  \n",
       "1        (4, 7)  (None, None)  (None, None)  (None, None)  1362.0  \n",
       "2  (None, None)  (None, None)  (None, None)  (None, None)  1870.5  \n",
       "3        (1, 5)        (2, 6)  (None, None)        (4, 2)  1741.0  \n",
       "4        (3, 4)  (None, None)        (5, 2)  (None, None)  1066.5  \n",
       "5        (6, 3)        (2, 6)        (6, 4)  (None, None)  1381.5  \n",
       "6  (None, None)  (None, None)  (None, None)  (None, None)  1757.5  \n",
       "7  (None, None)  (None, None)  (None, None)  (None, None)  1545.5  \n",
       "8        (4, 5)  (None, None)        (5, 5)  (None, None)  1853.5  \n",
       "9  (None, None)        (3, 6)  (None, None)  (None, None)  1767.0  \n",
       "\n",
       "[10 rows x 36 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "marked-plane",
   "metadata": {},
   "source": [
    "### Dataset Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "indirect-trigger",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export the preprocessed dataset.\n",
    "df_export = df.copy()\n",
    "df_export.to_csv(\"chess_app.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "traditional-hearing",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
